package org.jeecg.common.util.ldarExcel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;


/**
 * 导出Excel 抽象类，实现基本导出方法
 *
 * @author qhou
 *
 * @param <T>
 */
public abstract class BaseExport<T> {
	private static final Log log = LogFactory.getLog(BaseExport.class);
	protected SimpleDateFormat dateSf = new SimpleDateFormat("yyyy-MM-dd");
	protected SimpleDateFormat dateTimeSf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

	/**
	 * 获取默认标题样式
	 *
	 * @param workbook
	 * @return
	 */
	public CellStyle getHeaderCellStyle(Workbook workbook) {
		// 生成一个样式
		CellStyle headerCellStyle = workbook.createCellStyle();
//		// 设置这些样式
		headerCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		headerCellStyle.setBorderBottom(BorderStyle.THIN);
		headerCellStyle.setBorderLeft(BorderStyle.THIN);
		headerCellStyle.setBorderRight(BorderStyle.THIN);
		headerCellStyle.setBorderTop(BorderStyle.THIN);

		Font fontHeader = workbook.createFont();
		fontHeader.setFontName("微软雅黑");
		fontHeader.setFontHeightInPoints((short) 10);
		fontHeader.setBold(true);
		headerCellStyle.setAlignment(HorizontalAlignment.LEFT);
		headerCellStyle.setFont(fontHeader);
		headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		return headerCellStyle;
	}
	/**
	 * 获取默认标题样式
	 *
	 * @param workbook
	 * @return
	 */
	public CellStyle getMicrosoftHeaderCellStyle(Workbook workbook) {
		// 生成一个样式
		CellStyle headerCellStyle = workbook.createCellStyle();
//		// 设置这些样式
		headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		headerCellStyle.setBorderBottom(BorderStyle.THIN);
		headerCellStyle.setBorderLeft(BorderStyle.THIN);
		headerCellStyle.setBorderRight(BorderStyle.THIN);
		headerCellStyle.setBorderTop(BorderStyle.THIN);


		Font fontHeader = workbook.createFont();
		fontHeader.setFontName("微软雅黑");
		fontHeader.setFontHeightInPoints((short) 11);
		fontHeader.setBold(true);
		headerCellStyle.setAlignment(HorizontalAlignment.CENTER);
		headerCellStyle.setFont(fontHeader);
		headerCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		headerCellStyle.setFillForegroundColor(IndexedColors.LIME.index);

		return headerCellStyle;
	}
	/**
	 * 获取默认单元格样式
	 *
	 * @param workbook
	 * @return
	 */
	public CellStyle getMicrosoftCellStyle(Workbook workbook) {
		// 生成并设置另一个样式
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成另一个字体
		Font cellFont = workbook.createFont();
		cellFont.setFontName("微软雅黑");
		cellFont.setBold(true);
		cellFont.setFontHeightInPoints((short) 11);
		// 把字体应用到当前的样式
		cellStyle.setFont(cellFont);

		return cellStyle;
	}
	public CellStyle getMicrosoftCellNothinStyle(Workbook workbook) {
		// 生成并设置另一个样式
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成另一个字体
		Font cellFont = workbook.createFont();
		cellFont.setFontName("微软雅黑");
		cellFont.setBold(true);
		cellFont.setFontHeightInPoints((short) 11);
		// 把字体应用到当前的样式
		cellStyle.setFont(cellFont);
		return cellStyle;
	}
	public CellStyle getMicrosoftSumCellStyle(Workbook workbook) {
		// 生成并设置另一个样式
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成另一个字体
		Font cellFont = workbook.createFont();
		cellFont.setFontName("微软雅黑");
		cellFont.setItalic(true);
		cellFont.setBold(true);
		cellFont.setFontHeightInPoints((short) 11);
		// 把字体应用到当前的样式
		cellStyle.setFont(cellFont);

		return cellStyle;
	}
	/**
	 * 获取默认单元格样式
	 *
	 * @param workbook
	 * @return
	 */
	public CellStyle getCellStyle(Workbook workbook) {
		// 生成并设置另一个样式
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
//		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 生成另一个字体
		Font cellFont = workbook.createFont();
		cellFont.setFontName("微软雅黑");
		cellFont.setBold(true);
		cellFont.setFontHeightInPoints((short) 10);
		// 把字体应用到当前的样式
		cellStyle.setFont(cellFont);

		return cellStyle;
	}


	/**
	 * 获取标题样式
	 *
	 * @param workbook
	 * @return
	 */
	public CellStyle getTitleCellStyle(Workbook workbook) {
		CellStyle cellStyle = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 12);
		font.setBold(true);
		font.setColor(IndexedColors.BLACK.index);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.index);

		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setFont(font);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		return cellStyle;
	}
	public CellStyle getMicrosoftTitleCellStyle(Workbook workbook) {
		CellStyle cellStyle = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 14);
		font.setBold(true);
		font.setColor(IndexedColors.BLACK.index);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle.setFillForegroundColor(IndexedColors.WHITE.index);

		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setFont(font);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		return cellStyle;
	}
	public CellStyle getMicrosoftTitleNotThinCellStyle(Workbook workbook) {
		CellStyle cellStyle = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setFontName("微软雅黑");
		font.setFontHeightInPoints((short) 14);
		font.setBold(true);
		font.setColor(IndexedColors.BLACK.index);
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle.setFillForegroundColor(IndexedColors.WHITE.index);

		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setFont(font);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

		return cellStyle;
	}
	/**
	 * 根据标题，表头（及对应的集合内属性），生成Excel文件流
	 *
	 * @param title
	 * @param headers
	 * @param dataSet
	 * @param out
	 */
	public Workbook exportExcel(String title, String[][] headers, Collection<T> dataSet, OutputStream out) {
		log.debug("Begin to generate export sream...");
		// 声明一个工作薄
		Workbook workbook = new XSSFWorkbook();
		// 生成一个表格
		Sheet sheet = workbook.createSheet(title);

		// 设置表格默认列宽度为15个字节
//		sheet.setDefaultColumnWidth((short) 15);
		CellStyle headerCellStyle = this.getHeaderCellStyle(workbook);
		CellStyle cellStyle = this.getCellStyle(workbook);

		//行编号
		int rowNum = 0;
		//标题
		Row row = row = sheet.createRow(rowNum++);
		row.setHeightInPoints(22);

		Cell tCell = row.createCell(0);
		tCell.setCellStyle(getTitleCellStyle(workbook));
		tCell.setCellValue(title);

		// 合并单元格
		CellRangeAddress region1 = new CellRangeAddress(rowNum - 1,
				(short) rowNum - 1, 0,
				(short) headers.length - 1);
		sheet.addMergedRegion(region1);

		// 产生表格标题行
		row = sheet.createRow(rowNum++);
		for (short i = 0; i < headers.length; i++) {
			Cell hcell = row.createCell(i);
			hcell.setCellStyle(headerCellStyle);
			XSSFRichTextString text = new XSSFRichTextString(headers[i][0]);
			hcell.setCellValue(text);
		}

		log.debug("Begin to generate export sream...data");
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataSet.iterator();
		while (it.hasNext()) {
			row = sheet.createRow(rowNum++);
			T t = (T) it.next();

			int cellIds = 0;
			for (short i = 0; i < headers.length; i++) {
				String getMethodName = this.getFieldGetMethodName(t, headers[i][1]);
				try {
					Class tCls = t.getClass();
					System.out.println(getMethodName);
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});

					Cell cell = row.createCell(cellIds++);
					cell.setCellStyle(cellStyle);

					if (value != null){
						// 判断值的类型后进行强制类型转换
						if (value instanceof Integer) {
							cell.setCellValue(Integer.parseInt(String.valueOf(value)));
						} else if (value instanceof Float) {
							cell.setCellValue(Float.parseFloat(String.valueOf(value)));
						} else if (value instanceof Double || value instanceof BigDecimal) {
							cell.setCellValue(Double.parseDouble(String.valueOf(value)));
						} else if (value instanceof Long) {
							cell.setCellValue(Long.parseLong(String.valueOf(value)));
						} else if (value instanceof Date) {
							Date date = (Date) value;
							cell.setCellValue(dateSf.format(date));
						} else if (value instanceof Timestamp) {
							Timestamp date = (Timestamp) value;
							cell.setCellValue(dateTimeSf.format(date));
						} else{
							cell.setCellValue(String.valueOf(value));
						}
					}else{
						cell.setCellValue("");
					}

				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			// 调整列宽
		}
		log.debug("Begin to generate export sream...data end");
		log.debug("Auto set column width start...");
		autoSetColumnWidth(sheet, 0, dataSet.size(), headers.length);
		log.debug("Auto set column width end...");
		try {
			workbook.write(out);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return workbook;
	}




	/**
	 * 根据内容自动适应列宽度
	 * @param sheet
	 * @param startRow
	 * @param maxRowNum
	 * @param maxColumn
	 */
	protected void autoSetColumnWidth(Sheet sheet, int startRow,
									  int maxRowNum, int maxColumn) {
		// 获取当前列的宽度，然后对比本列的长度，取最大值
		for (int columnNum = 0; columnNum <= maxColumn; columnNum++) {
			sheet.autoSizeColumn(columnNum);

			int columnWidth = sheet.getColumnWidth(columnNum) / 256;

			for (int rowNum = startRow; rowNum <= maxRowNum; rowNum++) {
				Row currentRow;
				// 当前行未被使用过
				if (sheet.getRow(rowNum) == null) {
					currentRow = sheet.createRow(rowNum);
				} else {
					currentRow = sheet.getRow(rowNum);
				}

				if (currentRow.getCell(columnNum) != null) {
					Cell currentCell = currentRow.getCell(columnNum);
					int length = 0;
					try {
						length = currentCell.toString().getBytes("UTF-8").length;
					} catch (UnsupportedEncodingException e) {
						e.printStackTrace();
					}
					if (columnWidth < length) {
						columnWidth = length;
					}
				}
			}
			sheet.setColumnWidth(columnNum, columnWidth * 256);
		}
	}

	/**
	 * 根据对象及属性名得到对应的get方法名
	 *
	 * @param t
	 * @param fieldName
	 * @return
	 */
	protected String getFieldGetMethodName(T t, String fieldName) {
		String getMethodName = null;
		Field[] fields = t.getClass().getDeclaredFields();
		for (short i = 0; i < fields.length; i++) {
			Field field = fields[i];
			String fn = field.getName();
			if (fn.equalsIgnoreCase(fieldName)) {
				getMethodName = "get" + fn.substring(0, 1).toUpperCase() + fn.substring(1);
				break;
			}
		}

		return getMethodName;
	}
}
